package ie.dcu.spca.model.dao;

import ie.dcu.spca.model.Bean;
import ie.dcu.spca.model.CommonOperation;
import ie.dcu.spca.model.DBconnection;
import ie.dcu.spca.model.Dao;
import ie.dcu.spca.model.bean.Bill;
import ie.dcu.spca.model.sql.billSQL;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.database.Cursor;

public class BillDao extends Dao implements CommonOperation {

    private Bill cursorToBill(Cursor cursor) {
        // get the index
        cursor.moveToNext();
        int idIndex = cursor.getColumnIndex("id");
        int user_idIndex = cursor.getColumnIndex("user_id");
        int locationIndex = cursor.getColumnIndex("location");
        int creat_atIndex = cursor.getColumnIndex("create_at");
        int update_atIndex = cursor.getColumnIndex("update_at");
        int delete_atIndex = cursor.getColumnIndex("delete_at");
        // get the data
        int id = cursor.getInt(idIndex);
        int user_id = cursor.getInt(user_idIndex);
        String location = cursor.getString(locationIndex);
        long creat_at = cursor.getLong(creat_atIndex);
        long update_at = cursor.getLong(update_atIndex);
        long delete_at = cursor.getLong(delete_atIndex);
        // return a construct a bill
        cursor.close();
        return new Bill(id, location, user_id, creat_at, update_at, delete_at);
    }

    private List<Bill> cursorToBillList(Cursor cursor) {
        List<Bill> billList = new ArrayList<Bill>();
        while (cursor.moveToNext()) {
            int idIndex = cursor.getColumnIndex("id");
            int user_idIndex = cursor.getColumnIndex("user_id");
            int locationIndex = cursor.getColumnIndex("location");
            int creat_atIndex = cursor.getColumnIndex("create_at");
            int update_atIndex = cursor.getColumnIndex("update_at");
            int delete_atIndex = cursor.getColumnIndex("delete_at");
            int id = cursor.getInt(idIndex);
            int user_id = cursor.getInt(user_idIndex);
            String location = cursor.getString(locationIndex);
            long creat_at = cursor.getLong(creat_atIndex);
            long update_at = cursor.getLong(update_atIndex);
            long delete_at = cursor.getLong(delete_atIndex);
            billList.add(new Bill(id, location, user_id, creat_at, update_at,
                    delete_at));
        }
        cursor.close();
        return billList;
    }

    public List<Bill> getBilListByLocation(String location) {
        DBconnection conn = DBconnection.getInstance();
        String sql = String.format(billSQL.getBilListByLocation, location);
        Cursor cursor = conn.querySql(sql);
        return this.cursorToBillList(cursor);
    }

    public List<Bill> getBilListByUserid(int id) {
        DBconnection conn = DBconnection.getInstance();
        String sql = String.format(billSQL.getBilListByUserid, id);
        Cursor cursor = conn.querySql(sql);
        return this.cursorToBillList(cursor);
    }

    public int insertBill(String location, int user_id) {
        DBconnection conn = DBconnection.getInstance();
        int id = this.selectMaxId() + 1;
        Object[] args={ id, location, user_id,
                (new Date()).getTime(), 0, 0};
        conn.executeSql(billSQL.insertBill,args);
        return id;
    }

    @Override
    public Bean selectById(int id) {
        // step 1 get the dbconnection
        DBconnection conn = DBconnection.getInstance();
        // step 2 make a sql
        String sql = String.format(billSQL.selectById, id);
        // step 3 query sql and get cursor
        Cursor cursor = conn.querySql(sql);
        // step 4 new a bean
        Bill returnBill = null;
        // step 5 make bean data
        returnBill = this.cursorToBill(cursor);
        // step 6 return a bean
        return returnBill;
    }

    @Override
    public int insertByBean(Bean bean) {
        DBconnection conn = DBconnection.getInstance();
        Bill bill = (Bill) bean;
        int id = bill.getId();
        if (this.existById(id))
            return -1;
        String location = bill.getLocation();
        int user_id = bill.getUser_id();
        long create_at = bill.getCreate_at().getTime();
        long update_at = bill.getUpdate_at().getTime();
        long delete_at = bill.getDelete_at().getTime();
        Object[] args={ id, location, user_id,
                create_at, update_at, delete_at};
        conn.executeSql(billSQL.insertByBean,args);
        return id;
    }

    @Override
    public boolean deleteById(int id) {
        DBconnection conn = DBconnection.getInstance();
        if (!this.existById(id))
            return false;
        Object[] args = {(new Date()).getTime(),
                id};
        conn.executeSql(billSQL.deleteById,args);
        return true;
    }

    @Override
    public boolean deleteByBean(Bean bean) {
        return deleteById(((Bill) bean).getId());
    }

    @Override
    public boolean updateByBean(Bean bean) {
        DBconnection conn = DBconnection.getInstance();
        Bill bill = (Bill) bean;
        int id = bill.getId();
        if (!this.existById(id))
            return false;
        String location = bill.getLocation();
        int user_id = bill.getUser_id();
        long create_at = bill.getCreate_at().getTime();
        long update_at = bill.getUpdate_at().getTime();
        long delete_at = bill.getDelete_at().getTime();
        Object[] args = {location, user_id,
                create_at, update_at, delete_at, id};
        conn.executeSql(billSQL.updateByBean,args);
        return true;
    }

    @Override
    public int selectMaxId() {
        DBconnection conn = DBconnection.getInstance();
        String sql = billSQL.selectMaxId;
        Cursor cursor = conn.querySql(sql);
        int maxid = 0;
        if (cursor.moveToNext()) {
            int idIndex = cursor.getColumnIndex("max");
            maxid = cursor.getInt(idIndex);
        }
        cursor.close();
        return maxid;
    }

    @Override
    public boolean existById(int id) {
        DBconnection conn = DBconnection.getInstance();
        String sql = String.format(billSQL.existById, id);
        Cursor cursor = conn.querySql(sql);
        boolean f = false;
        if (cursor.moveToNext())
            f = true;
        cursor.close();
        return f;
    }

    @Override
    public boolean isDeletedById(int id) {
        DBconnection conn = DBconnection.getInstance();
        String sql = String.format(billSQL.isDeletedById, id);
        Cursor cursor = conn.querySql(sql);
        boolean f = false;
        if (cursor.moveToNext())
            f = true;
        cursor.close();
        return f;
    }

    @Override
    public List<Bean> selectAllBean() {
        DBconnection conn = DBconnection.getInstance();
        String sql = billSQL.selectAllBean;
        Cursor cursor = conn.querySql(sql);
        List<Bill> billist = this.cursorToBillList(cursor);
        List<Bean> beanlist = new ArrayList<Bean>();
        for (Bill b : billist) {
            beanlist.add(b);
        }
        cursor.close();
        return beanlist;
    }

}
